﻿using FileImportSystem2.Db.Entities;
using Microsoft.Data.Sqlite;
using MiniExcelLibs;
using System;
using System.Collections.Generic;
using System.DirectoryServices.ActiveDirectory;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace FileImportSystem2.Services
{
    internal class dbExportServer : IDisposable
    {
        private readonly string _dbFd = AppConfig.dbMonFd; // 数据库文件夹路径
        private readonly string _dbPath;// 数据库文件路径
        private readonly SqliteConnection _db = null!;// 数据库连接
        private readonly IProgress<string>? _progress;// 进度展示接口
        public bool _isInit = true;// 初始化状态
        public string _initError = string.Empty;// 初始化错误信息
        public string _dbMonth = string.Empty;// 数据库月份
        public string _dtStr = string.Empty;// 输出文件夹名称的日期
        public string _expDir = string.Empty;// 输出路径

        #region 实现 IDisposable 接口
        //IDisposable是 .NET 中的一个重要接口，它提供了一种​​确定性的资源释放机制​​，主要用于管理和释放非托管资源
        //.NET 的垃圾回收器（GC）能自动管理托管内存的释放，但它无法自动释放非托管资源。非托管资源是指不受 CLR（公共语言运行时）直接管理的资源
        //如果这些资源不及时释放，会导致内存泄漏和资源耗尽。IDisposable接口通过 Dispose()方法让开发者能够​​显式地、及时地​​释放这些资源
        private bool _disposed = false;

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    // 释放托管资源
                    _db?.Close();
                    _db?.Dispose();
                }
                _disposed = true;
            }
        }
        #endregion

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dateMonth">数据月</param>
        /// <param name="progress">进度展示</param>
        /// <param name="dtStr">输出文件夹名称的日期</param>
        /// <param name="expDir">输出路径</param>
        public dbExportServer(string dateMonth, IProgress<string>? progress = null, string dtStr = "", string expDir = "")
        {
            _dbMonth = dateMonth;
            _dbPath = Path.Combine(_dbFd, _dbMonth + ".db");
            _progress = progress;
            // 关键字段
            _dtStr = string.IsNullOrEmpty(dtStr) ? DateTime.Now.ToString("yyyyMMdd_HHmmss") : dtStr;
            _expDir = string.IsNullOrEmpty(expDir) ? Path.Combine(AppConfig.GetSafeAppDataPath(), "exp", _dtStr) :expDir ;
            Directory.CreateDirectory(_expDir);
            // 检查数据库路径
            if (!File.Exists(_dbPath))
            {
                _isInit = false;
                _initError = "数据库文件不存在";
                _progress?.Report($"导出数据异常:{_initError}");
                return;
            }
            // 初始化数据库连接
            _db = new SqliteConnection($"Data Source={_dbPath}");
            _db.Open();
        }

        /// <summary>
        /// 根据Sql语句导出数据到Excel文件
        /// <param name="sql">查询SQL语句</param>"
        /// <param name="filePath">输出文件路径</param>"
        /// <param name="outputDir">输出目录</param>"
        /// <param name="fileName">输出文件名</param>"
        /// <param name="rowCount">预估行数</param>"
        /// </summary>  
        public bool TryExportData(string sql, out string filePath, string outputDir = "", string fileName = "", Int64 rowCount = -1)
        {
            filePath = string.Empty;
            // 检查初始化状态
            if (_isInit == false)
            {
                _progress?.Report($"导出数据异常:{_initError}");
                filePath = string.Empty;
                return false;
            }
            // 确认输出路径
            if (outputDir == "")
            {
                outputDir = _dbFd;
            }

            if (!Directory.Exists(outputDir))
            {
                Directory.CreateDirectory(outputDir);
            }

            // 确认输出文件名
            if (fileName == "")
            {
                fileName = $"export_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx";
            }

            // 拼接
            string fullPath = Path.Combine(outputDir, fileName);

            // 删除已存在的文件
            if (File.Exists(fullPath))
            {
                try
                {
                    _progress?.Report($"删除已存在的文件:{fullPath}");
                    File.Delete(fullPath);
                }
                catch (Exception ex)
                {
                    _progress?.Report($"删除失败:{ex.Message}");
                    filePath = string.Empty;
                    return false;
                }
            }
            // 执行查询
            try
            {
                using var cmd = _db.CreateCommand();
                cmd.CommandText = sql;
                if (rowCount > 0 && rowCount <= 1040000)
                {
                    // 104W 内快速导出
                    using (var reader = cmd.ExecuteReader())
                    {
                        MiniExcel.SaveAs(fullPath, reader, true,overwriteFile:true);
                    }

                    filePath = fullPath;
                    return true;
                }
                else
                {
                    using var reader = cmd.ExecuteReader();
                    int batchSize = 1040000; // 每批100万行
                    int fileIndex = 1;
                    int currentRow = 0;
                    var batchData = new List<IDictionary<string, object>>();

                    while (reader.Read())
                    {
                        var rowData = new Dictionary<string, object>();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            rowData[reader.GetName(i)] = reader.GetValue(i);
                        }
                        batchData.Add(rowData);
                        currentRow++;

                        // 每达到100万行或最后一批数据时保存文件
                        if (currentRow % batchSize == 0 || !reader.HasRows)
                        {
                            string batchFilePath = Path.Combine(
                                 outputDir,
                                  $"{Path.GetFileNameWithoutExtension(fullPath)}_{fileIndex}{Path.GetExtension(fullPath)}"
                              );

                            MiniExcel.SaveAs(batchFilePath, batchData, true);

                            batchData.Clear();
                            fileIndex++;

                            if (filePath.Length > 0)
                            {
                                filePath = $"{filePath},{batchFilePath}";
                            }
                            else
                            {
                                filePath = batchFilePath;
                            }
                        }
                    }

                    // 处理最后一批不足100万行的数据
                    if (batchData.Any())
                    {
                        string lastBatchFilePath = Path.Combine(
                           outputDir,
                            $"{Path.GetFileNameWithoutExtension(fullPath)}_{fileIndex}{Path.GetExtension(fullPath)}"
                        );
                        MiniExcel.SaveAs(lastBatchFilePath, batchData, true);


                        if (filePath.Length > 0)
                        {
                            filePath = $"{filePath},{lastBatchFilePath}";
                        }
                        else
                        {
                            filePath = lastBatchFilePath;
                        }
                    }
                }



                return true;
            }
            catch (SqliteException ex)
            {
                _progress?.Report($"SQL执行失败: {ex.Message}");
                filePath = string.Empty;
                return false;
            }
            catch (Exception ex)
            {
                _progress?.Report($"导出过程异常: {ex.Message}");
                filePath = string.Empty;
                return false;
            }
        }

        /// <summary>
        /// 根据SQL语句获取数据量
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public Int64 GetDataCount(string sql)
        {
            // 检查初始化状态
            if (_isInit == false)
            {
                _progress?.Report($"获取数据量异常:{_initError}");
                return -1;
            }
            try
            {
                using var cmd = _db.CreateCommand();
                cmd.CommandText = sql;
                long recordCount = Convert.ToInt64(cmd.ExecuteScalar());
                return recordCount;
            }
            catch (SqliteException ex)
            {
                _progress?.Report($"SQL执行失败:{sql} / {ex.Message}");
                return -1;
            }
            catch (Exception ex)
            {
                _progress?.Report($"获取数据量过程异常: {ex.Message}");
                return -1;
            }
        }

        /// <summary>
        /// 导出未匹配数据
        /// </summary>
        /// <param name="outputDir">输出路径</param>
        /// <returns></returns>
        public bool TryExportUnMatchedData(out string outputDir)
        {

            outputDir = Path.Combine(_expDir, "未匹配数据");

            var exportTasks = new string[][]
            {
                new string[] { "日期,单号,扫描人,发件仓", "from senderdata where isMatched = 0", $"{_dbMonth}_发件数据_UnMatched"},
                new string[] {"日期,单号,结算对象,揽收业务员,分部,Is分部Matched,发件仓,Is发件仓Matched",
                             "from ShenzhouData where Is发件仓Matched = 0 or Is分部Matched = 0",$"{_dbMonth}_神州数据_UnMatched"},
                new string[] { "日期,单号,结算对象,分部", "from OutboundInvoiceData where IsMatched = 0", $"{_dbMonth}_出港账单_UnMatched"},
                new string[] { "日期,单号,结算对象,分部", "from CenterTransferFeeData where IsMatched = 0", $"{_dbMonth}_中转费_UnMatched"},
                new string[] {"*", "from UnMatchedData where 计算对象='' or 分部='' or 发件仓=''", $"{_dbMonth}_补充库_UnMatched" }

            };

            return ExecuteExportTasks(exportTasks, outputDir, _dtStr,"输出未匹配任务");
        }


        /// <summary>
        /// 导出统计结果数据
        /// </summary>
        /// <param name="outputDir"></param>
        /// <returns></returns>
        public bool TryExportResultData(out string outputDir)
        {

            outputDir = Path.Combine(_expDir,"分部数据");
            bool isHaveHz = false;
            isHaveHz = TryGetTableHz();
            
            if (!TryGetFbDt(out List<string> FbList, out List<string> DtList))
            {
                // 分部列表获取失败
                _progress?.Report("数据初始化失败，请检查错误日志");
                return false;
            }
            else
            {
                // 按分部导出数据
                foreach (var fb in FbList)
                {
                    // 确认输出目录
                    string effectiveFb = string.IsNullOrEmpty(fb) ? "空白" : fb;
                    string fbOutputDir = Path.Combine(outputDir, effectiveFb,"统计数据");
                    // 输出统计数据
                   var exportTasksFb = new string[][]
                    {
                        new string[] {
                            " 分部,结算对象,电子面单账号名称,count(单号) as 单号数量,sum(结算重量_kg) as 总结算重量,sum(派费合计) as 总派费合计,sum(中转费合计) as 总中转费合计",
                            $" from OutboundInvoiceData where 分部 = '{fb}' group by 分部,结算对象,电子面单账号名称",
                            "出港账单统计"
                        },
                        new string[] {
                            " 分部,结算对象,客户名称,count(单号) as 单号数量,sum(中转费合计) as 总中转费,sum(结算重量) as 总结算重量",
                            $" from CenterTransferFeeData where 分部 = '{fb}' group by 分部,结算对象,客户名称"
                            , "中心中转费统计"
                        },
                        new string[] {
                            "*",
                            $" from hz where 分部 = '{fb}'",
                            "汇总表" 
                        }
                    };  
                    // 执行导出
                    ExecuteExportTasks(exportTasksFb, fbOutputDir, _dtStr, $"分部[{effectiveFb}]统计数据导出");

                    // 输出明细数据
                    // 按日期输出
                    string detailOutputDir = Path.Combine(outputDir, effectiveFb, "明细数据");
                    foreach (var dt in DtList)
                    {
                        var detailExportTasks = new string[][]
                        {
                            new string[] { "*", $"from ShenzhouData where 分部 = '{fb}' and 日期 = '{dt}'", $"神州明细_{dt}"},
                            new string[] { "*", $"from OutboundInvoiceData where 分部 = '{fb}' and 日期 = '{dt}'", $"出港账单_{dt}"},
                            new string[] { "*", $"from CenterTransferFeeData where 分部 = '{fb}' and 日期 = '{dt}'", $"中心中转费_{dt}"}
                        };
                        ExecuteExportTasks(detailExportTasks, detailOutputDir, _dtStr, $"分部[{effectiveFb}] 日期[{dt}] 明细数据导出");
                    }

                }


                return true;
            }

            
        }

        /// <summary>
        /// 执行输出任务
        /// </summary>
        /// <param name="tasks"></param>
        /// <param name="outputDir"></param>
        /// <param name="dtStr"></param>
        /// <returns></returns>
        private bool ExecuteExportTasks(string[][] tasks, string outputDir, string dtStr,string taskName = "")
        {
            if (!_isInit)
            {
                _progress?.Report($"数据库初始化异常:{_initError}");
                return false;
            }

            long totalCount = 0;
            bool hasDataToExport = false;

            // 预检查是否有数据需要导出
            foreach (var task in tasks)
            {
                if (GetDataCount($"select count(*) {task[1]}") > 0)
                {
                    hasDataToExport = true;
                    break;
                }
            }

            if (!hasDataToExport)
            {
                _progress?.Report($"{taskName}没有需要导出的数据");
                return false;
            }

            if (!Directory.Exists(outputDir))
            {
                Directory.CreateDirectory(outputDir);
            }

            foreach (string[] task in tasks)
            {
                string field = task[0].Trim();
                string sqlRight = task[1];
                string sql = $"select {field} {sqlRight}";
                string fileNamePrefix = task[2];

                long recordCount = GetDataCount($"select count(*) {sqlRight}");
                if (recordCount > 0)
                {
                    totalCount += recordCount;
                    try
                    {
                        string fileName = $"{fileNamePrefix}_{recordCount}_{dtStr}.xlsx";
                        if (TryExportData(sql, out string expF, outputDir, fileName, recordCount))
                        {
                            _progress?.Report($"✓ 导出成功: {Path.GetFileName(expF)}, 记录数: {recordCount}");
                        }
                        else
                        {
                            _progress?.Report($"✗ 导出失败: {fileNamePrefix}");
                        }
                    }
                    catch (Exception ex)
                    {
                        _progress?.Report($"✗ 导出异常 {fileNamePrefix}: {ex.Message}");
                    }
                }
            }

            _progress?.Report(totalCount > 0 ?
                $"导出完成，总记录数: {totalCount}" :
                $"{taskName}没有数据被导出");

            return totalCount > 0;
        }


        /// <summary>
        /// 生成汇总表
        /// </summary>
        /// <returns></returns>
        private bool TryGetTableHz()
        {
            try
            {
                RunSql(_db, "drop table if exists Hz");
                RunSql(_db, "create table if not exists Hz( " +
                    "key string UNIQUE ," +
                    "日期 string , " +
                    "分部 string," +
                    "发件量 float," +
                    "有偿派费 float," +
                    "中心中转费 float, " +
                    "旺季中转费 float)");

                // 神州明细按日期统计数量
                RunSqlInTransaction(_db,
                    "insert into Hz (key,日期,分部, 发件量) " +
                    "select 日期 || '-' || 分部 as key,日期,分部,(count(单号)) as 发件量 " +
                    "from shenzhoudata " +
                    "group by 日期,分部 ");

                // 出港账单按日期求和派费合计
                RunSqlInTransaction(_db,
                    "insert into hz(key,日期,分部) select distinct 日期 || '-' || 分部 as key,日期,分部 from OutboundInvoiceData where 日期 || '-' || 分部 not in (select key from Hz)"
                    );

                RunSqlInTransaction(_db,
                    "update Hz set 有偿派费 = ( " +
                    "select sum(派费合计) from OutboundInvoiceData o " +
                    "where o.日期 || '-' || o.分部 = Hz.日期 || '-' || Hz.分部 ) ");

                // 中心中转费按日期求和中转费合计
                RunSqlInTransaction(_db,
                    "insert into hz(key,日期,分部) select distinct 日期 || '-' || 分部 as key,日期,分部 from CenterTransferFeeData where 日期 || '-' || 分部 not in (select key from Hz)"
                    );
                RunSqlInTransaction(_db,
                    "update Hz set 中心中转费 = ( " +
                    "select sum(中心中转费) from CenterTransferFeeData o " +
                    "where o.日期 || '-' || o.分部 = Hz.日期 || '-' || Hz.分部 ) ");

                // 中心中转费 按日期统计中转费>0的数量 * 0.1
                RunSqlInTransaction(_db,
                    "update Hz set 旺季中转费 = ( " +
                    "select count(单号)*0.1 from CenterTransferFeeData o " +
                    "where o.日期 || '-' || o.分部 = Hz.日期 || '-' || Hz.分部)");

                return true;
            }
            catch (Exception ex)
            {
                _progress?.Report($"更新汇总数据异常: {ex.Message}");
                return false;
            }

        }


        private bool TryGetFbDt(out List<string> fbList, out List<string> dtList)
        {
            string[] tabList = { "shenzhoudata", "CenterTransferFeeData", "OutboundInvoiceData" };
            fbList = new List<string>();
            dtList = new List<string>();
            try
            {
                RunSql(_db, "drop table if exists FbDtList");
                RunSql(_db, "create table if not exists FbDtList( " +
                   "key string UNIQUE ," +
                    "日期 string , " +
                    "分部 string)");


                // 出港账单按日期求和派费合计
                for (int i = 0; i < tabList.Length; i++)
                {
                    string sql = $"insert into FbDtList(key,日期,分部) select distinct 日期 || '-' || 分部 as key,日期,分部 from {tabList[i]} where 日期 || '-' || 分部 not in (select key from FbDtList)";
                    RunSqlInTransaction(_db,
                    sql
                    );
                }


                // 方案1：使用单个查询返回多个结果集
                using var cmd = _db.CreateCommand();
                cmd.CommandText = @"
                        SELECT DISTINCT 分部 FROM FbDtList  ;
                        SELECT DISTINCT 日期 FROM FbDtList  ;";

                using var reader = cmd.ExecuteReader();

                // 读取第一个结果集（分部）
                while (reader.Read())
                {
                    fbList.Add(reader.GetString(0));
                }

                // 移动到下一个结果集
                if (reader.NextResult())
                {
                    // 读取第二个结果集（日期）
                    while (reader.Read())
                    {
                        dtList.Add(reader.GetString(0));
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                _progress?.Report($"获取【分部/时间】列表异常: {ex.Message}");
                return false;
            }


        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="db"></param>
        /// <param name="sql"></param>
        private void RunSql(SqliteConnection db, string sql)
        {

            var cmd = new SqliteCommand(sql, db);
            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 在事务中执行sql语句
        /// </summary>
        /// <param name="db"></param>
        /// <param name="sql"></param>
        private void RunSqlInTransaction(SqliteConnection db, string sql)
        {
            using SqliteTransaction transaction = db.BeginTransaction();
            {
                using SqliteCommand cmd = new SqliteCommand(sql, db);
                {
                    cmd.Transaction = transaction;
                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();
            }
        }
    }
}
